# -*- coding: utf-8 -*-

'''
@author: Gil Duarte 6424
@date: 01/12/2014
@version final delivery
'''

import xlrd
import sqlite3
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

book = xlrd.open_workbook ('IPC_Portugal_1977_2013.xls')
activeSheet = book.sheet_by_name('Dados')

# establishing database connection
db = create_engine('sqlite:///IPC_Portugal_1977_2013.db')

mydb = declarative_base()

class Data (mydb):
  __tablename__ = "dados"

  ano = Column (Integer, primary_key = True)
  ipc_global_1981_100 = Column (Integer)
  taxa_de_inflacao = Column (Integer)
  remuneracao_minima_mensal = Column (Integer)
  remuneracao_maxima_mensal = Column (Integer)
  pib_per_capita_anual = Column (Integer)
  rendimento_nacional_bruto_per_capita = Column (Integer)
  rendimento_disponivel_bruto_per_capita = Column (Integer)
  remuneracoes_per_capita = Column (Integer)

  def __init__(self, ano, ipc, variacao_anual_taxa, rem_min, rem_max, pib, ren_nacional, ren_disp, remuneracoes):
    self.ano = ano
    self.ipc_global_1981_100 = ipc
    self.taxa_de_inflacao = variacao_anual_taxa
    self.remuneracao_maxima_mensal = rem_min
    self.remuneracao_maxima_mensal = rem_max
    self.pib_per_capita_anual = pib
    self.rendimento_nacional_bruto_per_capita = ren_nacional
    self.rendimento_disponivel_bruto_per_capita = ren_disp
    self.remuneracoes_per_capita = remuneracoes

#create a database previously defined
mydb.metadata.drop_all(db)
mydb.metadata.create_all(db)

#DBSession establishes all conversations with the database
DBSession = sessionmaker(bind=db)
session = DBSession()

values = []
#inserting the values in table
for row in range (1, activeSheet.nrows):
	for col in range (1, activeSheet.ncols):
		values.append(activeSheet.cell_value(row, col))
	session.add(Data(values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8]))
	values = []
	
session.commit ()

#feedback for the user in the first run

print "Creating database"
for x in range (1, 100):
	print x, " %"
print "Database created sucessfully"


# #open sqlite3 connection
# connection = sqlite3.connect('IPC_Portugal_1977_2013.db')
# cursor = connection.cursor()

# #create table
# cursor.execute('''DROP TABLE IF EXISTS Dados''')
# cursor.execute('''CREATE TABLE Dados (ano, ipc_global_1981_100,
#                                       taxa_de_inflacao,
#                                       remuneracao_minima_mensal,
#                                       remuneracao_maxima_mensal,
#                                       pib_per_capita_anual,
#                                       rendimento_nacional_bruto_per_capita,
#                                       rendimento_disponivel_bruto_per_capita,
#                                       remuneracoes_per_capita)''')

# #insert values in table
# values = []
# for row in range(1, activeSheet.nrows):
#     for col in range(1, activeSheet.ncols):
#         values.append(activeSheet.cell_value(row, col))  
#     cursor.execute(
#         "INSERT INTO Dados VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", values)
#     values = []

# connection.commit()
# connection.close()
